/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLayer;

import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Claus
 */
public class DBSupplier implements IFDBSupplier
{

	private Connection con;

	/** Creates a new instance of DBCustomer */
	public DBSupplier()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	public int deleteSupplier(int supplierID)
	{
		int rc = -1;

		String query = "DELETE FROM Supplier WHERE supplierID = "
				+ supplierID;
		System.out.println(query);
		try
		{ // delete from customer
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
			con.setAutoCommit(true);
		}//slut try
		catch (Exception ex)
		{
			System.out.println("Delete exception in customer db: " + ex);

			try
			{
				rc = -1;
				con.rollback();
				con.setAutoCommit(true);
			}
			catch (Exception ex1)
			{
				System.out.println("Error rolling back" + ex1);
			}
		}
		return (rc);
	}

	public Supplier findSupplier(int id, boolean retriveAssociation)
	{
		String wClause = "  supplierID = " + id;
		return singleWhere(wClause, retriveAssociation);
	}

	public ArrayList<Supplier> getAllSuppliers(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}

	public int insertSupplier(Supplier sup)
	{
		//call to get the next supplier ID
		int nextID = GetMax.getMaxId("Select max(supplierID) from supplier");
		nextID = nextID + 1;
		System.out.println("next supplierID = " + nextID);

		int rc = -1;
		String query = "INSERT INTO supplier(supplierID, name, address, cCode, phoneNumber)  VALUES("
				+ nextID + ",'"
				+ sup.getName() + "','"
				+ sup.getAddress() + "','"
				+ sup.getCountryCode() + "','"
				+ sup.getPhoneNo() + "','"
				+ sup.getEmail();

		System.out.println("insert : " + query);


		try
		{
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
			con.setAutoCommit(true);
		}
		catch (SQLException ex)
		{
			System.out.println("Supplier ikke oprettet");
			System.out.println("Insert exception in supplier db: " + ex);
			try
			{
				rc = -1;
				con.rollback();
				con.setAutoCommit(true);
			}
			catch (Exception ex1)
			{
				System.out.println("Error rolling back" + ex1);
			}
		}
		return (rc);
	}

	public Supplier searchSupplierName(String attValue, boolean retriveAssociation)
	{
		String wClause = "name like '%" + attValue + "%'";
		System.out.println("SearchSupplier " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	public int updateSupplier(Supplier sup)
	{
		Supplier supObj = sup;

		int rc = -1;

		String query = "UPDATE supplier SET "
				+ "name ='" + supObj.getName() + "', "
				+ "address ='" + supObj.getAddress() + "', "
				+ "cCode ='" + supObj.getCountryCode() + "', "
				+ "phoneNumber ='" + supObj.getPhoneNo() + "', "
				+ "email = '" + supObj.getEmail() + "' "
				+ " WHERE supplierID = " + supObj.getSupplierID();

		System.out.println("Update query:" + query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}
		catch (Exception ex)
		{
			System.out.println("Update exception in customer db: " + ex);
		}
		return (rc);
	}

	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Supplier> list = new ArrayList<Supplier>();
		String query = buildQuery(wClause);

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next())
			{
				Supplier cusObj = new Supplier();
				cusObj = buildSupplier(results);
				list.add(cusObj);
			}

			stmt.close();

			if (retrieveAssociation)
			{
				DBCountry dbCount = new DBCountry();
				for (Supplier supObj : list)
				{
					String countryCode = supObj.getCountryCode();
					String countryName = dbCount.getCountryName(countryCode);
					supObj.setCountry(countryName);
				}
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier supObj = new Supplier();

		String query = buildQuery(wClause);
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next())
			{
				supObj = buildSupplier(results);

				stmt.close();
				if (retrieveAssociation)
				{
					String countryCode = supObj.getCountryCode();
					DBCountry dbCount = new DBCountry();
					String countryName = dbCount.getCountryName(countryCode);
					supObj.setCountry(countryName);
				}
			}
			else
			{
				supObj = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return supObj;
	}

	private String buildQuery(String wClause)
	{
		String query = "SELECT supplierID, name, address, cCode, phoneNumber, email FROM supplier";

		if (wClause.length() > 0)
		{
			query = query + " WHERE " + wClause;
		}

		return query;
	}

	private Supplier buildSupplier(ResultSet results)
	{
		Supplier supObj = new Supplier();
		try
		{
			supObj.setSupplierID(results.getInt("supplierID"));
			supObj.setName(results.getString("name"));
			supObj.setAddress(results.getString("address"));
			supObj.setCountry(results.getString("cCode"));
			supObj.setPhoneNo(results.getString("phoneNumber"));
			supObj.setEmail(results.getString("email"));
		}
		catch (Exception e)
		{
			System.out.println("error in building the customer object");
		}
		return supObj;
	}
}
